﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spCtrlmExecuteScheduledJob]
#-- Purpose:		Provides a mechanism to call a scheduled job, wait
#--					for completion, and return the sysout results
#--	Last Update:	08/17/2011
#--					For a complete history - please review comments in SVN
#-- Called By:		Scheduled Job
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spCtrlmExecuteScheduledJob]
(
	@job_name	sysname,
	@job_label	varchar(128)	= NULL,
	@wait_time	datetime		= '00:00:05'
)
AS

-----------------------------------------------
--- http://www.mssqltips.com/tip.asp?tip=2167
-----------------------------------------------
--- Requires the "Log to Table" on Job Step to 
---		be turned on.
-----------------------------------------------

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@job_id uniqueidentifier, @job_owner sysname, @log nvarchar(MAX), @return_value int,
		@start_date datetime

--- Initialize Local Variables		
SELECT	@start_date = GETDATE()

--- Print Start Time
PRINT 'Job [' + @job_name + '] started at ' + CONVERT(varchar(20), @start_date, 120)
PRINT ''

--- Createing TEMP TABLE
CREATE TABLE #xp_results 
(
	job_id					uniqueidentifier NOT NULL,
	last_run_date			int NOT NULL,
	last_run_time			int NOT NULL,
	next_run_date			int NOT NULL,
	next_run_time			int NOT NULL,
	next_run_schedule_id	int NOT NULL,
	requested_to_run		int NOT NULL, -- BOOL
	request_source			int NOT NULL,
	request_source_id		sysname COLLATE database_default NULL,
	running					int NOT NULL, -- BOOL
	current_step			int NOT NULL,
	current_retry_attempt	int NOT NULL,
	job_state				int NOT NULL
)

--- Retrieve the Job ID and Job Owner for @job_name
SELECT	@job_id = sj.job_id,
		@job_owner = SUSER_NAME(sj.owner_sid)
FROM	msdb.dbo.sysjobs sj
WHERE	sj.name = @job_name

--- Determine current status
INSERT INTO #xp_results
	EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = @job_owner, @job_id = @job_id

--- Loop until any existing job completes
WHILE EXISTS (SELECT TOP 1 * FROM #xp_results WHERE running = 1)
  BEGIN
	-- Give a time length to wait before checking again (from parameter @wait_time)
	WAITFOR DELAY @wait_time

	--- Determine current status
	DELETE FROM #xp_results
	INSERT INTO #xp_results
		EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = @job_owner, @job_id = @job_id
  END

--- Start the job if the job is not running
IF @job_label IS NOT NULL
  BEGIN
	--- Add Label Information
	INSERT INTO dbo.tblControlmExecution VALUES (@job_name, @start_date, @job_label)
  END
EXEC	msdb.dbo.sp_start_job @job_name = @job_name
WAITFOR DELAY '00:00:02'

--- Determine if the new job has started and is still running
DELETE FROM #xp_results
INSERT INTO #xp_results
	EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = @job_owner, @job_id = @job_id
  
--- Loop until the job completes
WHILE EXISTS (SELECT TOP 1 * FROM #xp_results WHERE running = 1)
  BEGIN
	-- Give a time length to wait before checking again (from parameter @wait_time)
	WAITFOR DELAY @wait_time

	--- Determine current status
	DELETE FROM #xp_results
	INSERT INTO #xp_results
		EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = @job_owner, @job_id = @job_id
  END
  
--- Cleanup
DROP TABLE #xp_results

--- Remove Label Information
DELETE FROM dbo.tblControlmExecution WHERE ctm_job_name = @job_name AND ctm_date_started = @start_date

--- Output the job log
DECLARE crs CURSOR FOR
	SELECT		jsl.log
	FROM		msdb.dbo.sysjobs j
	JOIN		msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
	LEFT JOIN	msdb.dbo.sysjobstepslogs jsl ON jsl.step_uid = js.step_uid
	WHERE		j.job_id = @job_id
	ORDER BY	js.step_id
OPEN crs
FETCH NEXT FROM crs INTO @log
WHILE @@FETCH_STATUS <> -1
  BEGIN
	PRINT @log
	FETCH NEXT FROM crs INTO @log
  END
CLOSE crs
DEALLOCATE crs


--- Print Start Time
PRINT ''
PRINT 'Job [' + @job_name + '] finished at ' + CONVERT(varchar(20), GETDATE(), 120)


--- Return the Job Status
SELECT	@return_value = (CASE WHEN run_status = 1 THEN 0 ELSE 1 END)
FROM	(
		SELECT		TOP 1 *
		FROM		msdb.dbo.sysjobhistory 
		WHERE		job_id = @job_id 
					AND step_id = 0 
		ORDER BY	(ROW_NUMBER() OVER (ORDER BY run_date DESC, run_time DESC))
		) t1
RETURN	@return_value